package com.chidopi.member.dao.impl;

import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import com.chidopi.member.dao.IMemberDao;
import com.chidopi.member.domain.Book2City;
import com.chidopi.member.domain.BookCities;
import com.chidopi.member.domain.BookItems;
import com.chidopi.member.domain.Member2Book;
import com.chidopi.member.domain.Member2Device;
import com.chidopi.member.domain.Member2Subscribe;
import com.chidopi.member.domain.Member2SubscribeHis;
import com.chidopi.member.domain.Members;
import com.chidopi.member.domain.Subscriptions;

public class MemberDaoImpl implements IMemberDao {
	private static Logger logger = LoggerFactory.getLogger(MemberDaoImpl.class);
	
	protected SimpleJdbcTemplate simpleJdbcTemplate;
	@Autowired
	public void init(DataSource datasource) {
		this.simpleJdbcTemplate = new SimpleJdbcTemplate(datasource);
		logger.debug("init: after new JdbcTemplate");
	}
	
	@Override
	public int insertMember2Device(Member2Device mem2dev) {
		String sql = "insert into member2device (email, city_id, device_name, genkey, enable_flag, city_version) values (:email, :city_id, :device_name, :genkey, :enable_flag, 0)";
		SqlParameterSource vo = new BeanPropertySqlParameterSource(mem2dev);
		return this.simpleJdbcTemplate.update(sql, vo);
	}

	@Override
	public int updateMember2Device(Member2Device mem2dev) {
		String sql = "update member2device set genkey = :genkey, enable_flag = :enable_flag, city_version = :city_version where email = :email and city_id = :city_id and device_name = :device_name";
		SqlParameterSource vo = new BeanPropertySqlParameterSource(mem2dev);
		return this.simpleJdbcTemplate.update(sql, vo);
	}

	@Override
	public List<Member2Device> findMember2Device(String email, int city_id) {
		String sql = "select * from member2device where email=? and city_id=?";
		RowMapper<Member2Device> rm = 
			ParameterizedBeanPropertyRowMapper.newInstance(Member2Device.class);
		return this.simpleJdbcTemplate.query(sql, rm, email, city_id);
	}

	@Override
	public int checkMember2Device(String email, int city_id, String device,
			String genkey) {
		String sql = "select enable_flag from member2device where email=? and city_id=? and device_name=? and genkey=?";
		List<Map<String, Object>> list = this.simpleJdbcTemplate.queryForList(sql, email, city_id, device, genkey);
		if (list == null || list.size()==0) {
			return 0;
		}
		String enable_flag = (String) list.get(0).get("ENABLE_FLAG");
		if (enable_flag == null) {
			return 0;
		} else if (enable_flag.equals("Y")) {
			return 1;
		} else {
			return -1;
		}
	}

	@Override
	public BookCities findBookCitiesById(int city_id) {
		String sql = "select a.*, b.* " +
			"from bookcities a, bookcityext b where a.id=b.city_id and b.city_id=? and a.enable_flag='Y'";
		RowMapper<BookCities> rm = 
			ParameterizedBeanPropertyRowMapper.newInstance(BookCities.class);
		List<BookCities> list = this.simpleJdbcTemplate.query(sql, rm, city_id);
		if (list == null || list.size()==0) return null;
		return list.get(0);
	}
	
	@Override
	public BookItems findBookItemsById(int book_id) {
		String sql = "select * from bookitems where id=? and enable_flag='Y'";
			RowMapper<BookItems> rm = 
				ParameterizedBeanPropertyRowMapper.newInstance(BookItems.class);
			List<BookItems> list = this.simpleJdbcTemplate.query(sql, rm, book_id);
			if (list == null || list.size()==0) return null;
			return list.get(0);
	}

	@Override
	public Member2Device findMember2DeviceById(String email, int city_id,
			String device) {
		String sql = "select * from member2device where email=? and city_id=? and device_name=? and enable_flag='Y'";
		RowMapper<Member2Device> rm = 
			ParameterizedBeanPropertyRowMapper.newInstance(Member2Device.class);
		List<Member2Device> list = this.simpleJdbcTemplate.query(sql, rm, email, city_id, device);
		if (list == null || list.size()==0) return null;
		return list.get(0);
	}

	@Override
	public Book2City findBook2CityByPK(int city_id, int book_id) {
		String sql = "select * from book2city where city_id=? and book_id=? and enable_flag='Y'";
		RowMapper<Book2City> rm = 
			ParameterizedBeanPropertyRowMapper.newInstance(Book2City.class);
		List<Book2City> list = this.simpleJdbcTemplate.query(sql, rm, city_id, book_id);
		if (list == null || list.size()==0) return null;
		return list.get(0);
	}

	@Override
	public int validateMember(String email, int city_id, String pwd) {
		String sql = "select * from member2city where email=? and city_id=? and enable_flag='Y' ";
		RowMapper<Members> rm = 
			ParameterizedBeanPropertyRowMapper.newInstance(Members.class);
		List<Members> list = this.simpleJdbcTemplate.query(sql, rm, email, city_id);
		if (list == null || list.size()==0) {
			return Members.NO_MEMBER;
		}
		if (!list.get(0).getPassword().equals(pwd)) {
			logger.debug("db    pwd=[" + list.get(0).getPassword() + "]");
			logger.debug("login pwd=[" + pwd + "]");
			return Members.PASSWORD_FAILURE;
		}
		return Members.NORMAL;
	}

	@Override
	public Members getMember(String email, int city_id) {
		String sql = "select * from member2city where email=? and city_id=? and enable_flag='Y'";
		RowMapper<Members> rm = 
			ParameterizedBeanPropertyRowMapper.newInstance(Members.class);
		List<Members> list = this.simpleJdbcTemplate.query(sql, rm, email, city_id);
		if (list == null || list.size()==0) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public int updateMember(Members member) {
//		String sql = "update members set ios_sub_id = :ios_sub_id, start_date = :start_date, end_date = :end_date where email = :email and city_id = :city_id and password :password ";
//		SqlParameterSource vo = new BeanPropertySqlParameterSource(member);
//		return this.simpleJdbcTemplate.update(sql, vo);
		return 0;
	}

	@Override
	public List<Member2Subscribe> findMember2Subscribe(String email, int city_id) {
		String sql = "select * from member2subscribe where email=? and city_id=? order by start_date desc";
		RowMapper<Member2Subscribe> rm = 
			ParameterizedBeanPropertyRowMapper.newInstance(Member2Subscribe.class);
		return this.simpleJdbcTemplate.query(sql, rm, email, city_id);
	}

	@Override
	public List<Member2Subscribe> findMember2SubscribeByNoStart(String email,
			int city_id) {
		String sql = "select * from member2subscribe where email=? and city_id=? and status=2 order by start_date desc";
		RowMapper<Member2Subscribe> rm = 
			ParameterizedBeanPropertyRowMapper.newInstance(Member2Subscribe.class);
		return this.simpleJdbcTemplate.query(sql, rm, email, city_id);
	}

	@Override
	public int insertMember2Subscribe(Member2Subscribe mem2sub) {
		String sql = "insert into member2subscribe (email, city_id, sub_id, start_date, end_date, status, overage) " 
			+ "values (:email, :city_id, :sub_id, :start_date, :end_date, :status, :overage)";
		SqlParameterSource vo = new BeanPropertySqlParameterSource(mem2sub);
		return this.simpleJdbcTemplate.update(sql, vo);
	}

	@Override
	public int insertMember2SubscribeHis(Member2SubscribeHis mem2subHis) {
		String sql = "insert into member2subscribehis (email, city_id, sub_id, order_date, start_date, end_date, overage) " 
			+ "values (:email, :city_id, :sub_id, :order_date, :start_date, :end_date, :overage)";
		SqlParameterSource vo = new BeanPropertySqlParameterSource(mem2subHis);
		return this.simpleJdbcTemplate.update(sql, vo);
	}

	@Override
	public int updateMember2Subscribe(Member2Subscribe mem2sub) {
		String sql = "update member2subscribe set end_date = :end_date, status = :status "
			+ "where email = :email and city_id = :city_id and start_date = :start_date and sub_id = :sub_id ";
		SqlParameterSource vo = new BeanPropertySqlParameterSource(mem2sub);
		return this.simpleJdbcTemplate.update(sql, vo);
	}

	@Override
	public Subscriptions findSubscriptionById(int id) {
		String sql = "select * from subscriptions where id=?";
		RowMapper<Subscriptions> rm = 
			ParameterizedBeanPropertyRowMapper.newInstance(Subscriptions.class);
		List<Subscriptions> list = this.simpleJdbcTemplate.query(sql, rm,id);
		if (list == null || list.size()==0) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public Member2Subscribe checkMember2Subscribe(String email, int city_id) {
		String sql = "select * from member2subscribe where email=? and city_id=? and status=1 order by start_date desc";
		RowMapper<Member2Subscribe> rm = 
			ParameterizedBeanPropertyRowMapper.newInstance(Member2Subscribe.class);
		List<Member2Subscribe> list = this.simpleJdbcTemplate.query(sql, rm, email, city_id);
		if (list == null || list.size() ==0) return null;
		return list.get(0);
	}

	@Override
	public int insertMember2Book(Member2Book mem2bok) {
		String sql = "insert into member2book (email, city_id, device_name, book_id, download_date) values (:email, :city_id, :device_name, :book_id, NOW())";
		SqlParameterSource vo = new BeanPropertySqlParameterSource(mem2bok);
		return this.simpleJdbcTemplate.update(sql, vo);
	}

	@Override
	public int updateMember2Book(Member2Book mem2bok) {
		String sql = "update member2book set genkey = :genkey where email = :email and city_id = :city_id and device_name = :device_name and book_id = :book_id";
		SqlParameterSource vo = new BeanPropertySqlParameterSource(mem2bok);
		return this.simpleJdbcTemplate.update(sql, vo);
	}

	@Override
	public List<Member2Book> findMember2Book(Member2Device mem2dev) {
		String sql = "select * from member2book where email=? and city_id=? and device_name=?";
		RowMapper<Member2Book> rm = 
			ParameterizedBeanPropertyRowMapper.newInstance(Member2Book.class);
		return this.simpleJdbcTemplate.query(sql, rm, mem2dev.getEmail(), mem2dev.getCity_id(), mem2dev.getDevice_name());
	}

	@Override
	public int getCityIDByRoleId(int role_id) {
		String sql = "select city_id from role2city where role_id=?";
		List<Map<String, Object>> list = this.simpleJdbcTemplate.queryForList(sql, role_id);
		if (list == null || list.size()==0) {
			return -1;
		}
		return Integer.parseInt((String) list.get(0).get("CITY_ID")) ;
	}	
	
}
